﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using RN.ERP.Web.PersonService;
using RN.ERP.Web.ModelService;
using Newtonsoft.Json;
using System.IO;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using System.Configuration;
using Telerik.Web.UI;

namespace RN.ERP.Web.Person
{
    public partial class ImportPersonInfoByExcel : BasePage
    {
        /// <summary>
        /// 存放上传excel的文件夹
        /// </summary>
        private const string EXCEL_FOLDER_NAME = "../Download/excel";
        /// <summary>
        /// 当前市场
        /// </summary>
        protected string AreaCode
        {
            get
            {
                return UserInfo.CurrentArea;
            }
        }

        public override bool IsCheckPermission
        {
            get
            {
                return false;
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                GetParams();
            }
        }

        private void GetParams()
        {
            if (Request.QueryString["groupId"] != null)
            {
                hdGroupID.Value = Request.QueryString["groupId"].Trim();
            }
        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            string fileName = this.fileUpload.PostedFile.FileName;
            string path = Server.MapPath(EXCEL_FOLDER_NAME);
            FileInfo finfo = new FileInfo(fileName);
            string ext = finfo.Extension;

            if (!".xls|.xlsx".Contains(ext))
            {
                RadWindowManager1.RadAlert("只能上传excel格式的文件！", 300, 150, "提示", "");
                return;
            }

            fileName = DateTime.Now.ToString("yyyyMMdd") + Guid.NewGuid() + ext;
            string filePath = path + "/" + fileName;
            this.fileUpload.PostedFile.SaveAs(filePath);

            ImportEmpInfoByExcel(filePath, hdGroupID.Value);
        }

        protected void btnDaochu_Click(object sender, EventArgs e)
        {
            HttpResponse resp;
            string filename = "错误描述.xls";
            filename = Server.UrlEncode(filename);
            resp = Page.Response;
            resp.ContentEncoding = System.Text.Encoding.UTF8;
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + filename);
            resp.Write(Session["fail"].ToString());
            resp.End();
        }

        /// <summary>
        /// 将人员信息excel中的数据导入数据库
        /// </summary>
        /// <param name="filePath"></param>
        private void ImportEmpInfoByExcel(string filePath, string groupType)
        {
            string msg = string.Empty;
            int groupID = 0;
            int.TryParse(groupType, out groupID);
            List<EmpCertificateImportEntity> errList = new List<EmpCertificateImportEntity>();
            List<EmpCertificateImportEntity> list = this.GetEmpInfoByExcel(filePath, groupID, out msg, ref errList);
            List<EmpCertificateImportEntity> failList = new List<EmpCertificateImportEntity>();

            if (list != null && list.Count > 0)
            {

                string typename = this.GetBigTypeNamebyGroupID(groupID);
                if (!string.IsNullOrEmpty(typename))
                {
                    List<EmpCertificateImportEntity> errTypeList = list.Where(r => r.ZSTypeName != typename).ToList();
                    list = list.Where(r => r.ZSTypeName == typename).ToList();
                    if (errTypeList != null)
                    {
                        errTypeList.ForEach(r => r.Description = "导入证书类型错误，应为：" + typename);
                        errList.AddRange(errTypeList);
                    }
                }
                string failEmpStr = string.Empty;
                using (PersonServiceClient client = new PersonServiceClient())
                {
                    try
                    {
                        string empStr = JsonConvert.SerializeObject(list);
                        failEmpStr = client.BatchImportPersonInfo(empStr, groupID, UserInfo.CorpUserGuid, ConfigurationManager.AppSettings["sysArea"], UserInfo.Name);
                        msg = "导入成功！";
                        if (!string.IsNullOrEmpty(failEmpStr))
                        {
                            failList = JsonConvert.DeserializeObject<List<EmpCertificateImportEntity>>(failEmpStr);
                        }
                    }
                    catch (Exception e)
                    {
                        msg = e.Message;
                    }
                }
            }
            try
            {

                if (failList != null && failList.Count > 0)
                {
                    if (errList != null & errList.Count > 0)
                        errList.AddRange(failList);
                }
                StringBuilder sb = new StringBuilder();
                sb.Append("证书类别\t姓名\t身份证号\t证书名称\t证书编号\t专业\t等级\t获取时间\t有效期到\t错误描述\n");
                foreach (EmpCertificateImportEntity model in errList)
                {
                    sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\t{7}\t{8}\t{9}\n",
                        model.ZSTypeName, model.Name, "@" + model.IDCard, model.ZSName, model.ZSCode, model.ZSspecialty, model.ZSLevel, model.ZSGetTime, model.ZSEndTime, model.Description);
                }
                Session["fail"] = sb.ToString();
                liEmp.Style["Display"] = "Block";
                ListEmps.DataSource = failList;
                ListEmps.DataBind();
            }
            catch (Exception e)
            {
                msg = e.Message;
            }

            RadWindowManager1.RadAlert(msg, 300, 150, "提示", "");
        }

        /// <summary>
        /// 从excel中获得待入库的人员信息
        /// </summary>
        /// <param name="uploadPath"></param>
        /// <returns></returns>
        public List<EmpCertificateImportEntity> GetEmpInfoByExcel(string uploadPath, int groupID, out string msg, ref List<EmpCertificateImportEntity> errList)
        {
            var list = new List<EmpCertificateImportEntity>();
            EmpCertificateImportEntity emp = null;
            int groupId = -1;
            int.TryParse(hdGroupID.Value, out groupId);

            try
            {
                DataTable dt = GetExcelTable(uploadPath);

                if (dt != null)
                {

                    foreach (DataRow row in dt.Rows)
                    {
                        bool isValid = true;
                        try
                        {
                            emp = new EmpCertificateImportEntity();
                            emp.ZSTypeName = row[0].ToString().Replace(" ", "");
                            emp.Name = row[1].ToString().Replace(" ", "");
                            emp.IDCard = row[2].ToString().Trim();
                            if (string.IsNullOrEmpty(emp.ZSTypeName) && string.IsNullOrEmpty(emp.Name) && string.IsNullOrEmpty(emp.IDCard))
                                break;
                            if (emp.IDCard.Length != 15 && emp.IDCard.Length != 18)
                            {
                                emp.Description = "无效身份证";
                                isValid = false;
                            }
                            emp.ZSName = row[3].ToString().Trim();
                            emp.ZSCode = row[4].ToString().Trim();
                            emp.ZSspecialty = row[5].ToString().Trim();
                            emp.ZSLevel = row[6].ToString().Trim();
                            emp.ZSTypeID = groupId;
                            DateTime time = DateTime.MinValue;
                            DateTime.TryParse(row[7].ToString(), out time);
                            emp.ZSGetTime = time;
                            time = DateTime.MinValue;
                            DateTime.TryParse(row[8].ToString(), out time);
                            emp.ZSEndTime = time;

                            if (isValid)
                                list.Add(emp);
                            else
                                errList.Add(emp);
                        }
                        catch (Exception ex)
                        {
                            if (emp != null)
                                errList.Add(emp);
                        }
                    }
                    if (list.Count == 0)
                    {
                        msg = "模板使用错误！";
                    }
                    else
                    {
                        msg = "true";
                    }
                }
                else
                {
                    msg = "excel中无数据或文件格式有误！";
                }
            }
            catch (Exception e)
            {
                msg = e.Message;
            }

            return list;
        }
        public DataTable GetExcelTable(string uploadPath)
        {
            DataSet ds = new DataSet();
            ////HDR为yes 则第一数据行为列名,为no则自动为列加列名F1  F2  F3
            string Xls_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
            OleDbConnection Conn = new OleDbConnection(Xls_ConnStr);
            try
            {
                Conn.Open();
                string sql_str = "select * from [Sheet1$]";
                OleDbDataAdapter da = new OleDbDataAdapter(sql_str, Conn);
                da.Fill(ds, "excel_data");
                Conn.Close();
            }
            catch
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }
                return null;
            }
            finally
            {
                Conn.Dispose();
            }

            if (ds == null)
            {
                return null;
            }

            if (ds.Tables.Count < 1)
            {
                return null;
            }

            return ds.Tables[0];
        }
        private string GetBigTypeNamebyGroupID(int groupID)
        {
            string name = string.Empty;
            switch (groupID)
            {
                case 1401:
                    name = "特种作业人员";
                    break;
                case 1402:
                    name = "关键岗位人员";
                    break;
                case 1403:
                    name = "职业技能人员";
                    break;
                case 1404:
                    name = "安全生产三类人员";
                    break;
                case 1405:
                    name = "";
                    break;
                case 1406:
                    name = "注册建造师";
                    break;
                case 1407:
                    name = "建筑业技师";
                    break;

            }
            return name;
        }

        protected void RadAjaxManager1_AjaxRequest(object sender, AjaxRequestEventArgs e)
        {
            if (e.Argument == "Rebind")
            {
                ListEmps.MasterTableView.SortExpressions.Clear();
                ListEmps.MasterTableView.GroupByExpressions.Clear();
                ListEmps.Rebind();
            }

        }
        protected void ListEmps_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
        {

        }
    }
}